﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using AMS.DataAccess.BO;
using AMS.DataAccess.Common;
using AMS.DataAccess.Filters;
using AMS.DataAccess.BO.Enums;
using System.Diagnostics;

namespace AMS.DataAccess.Sql
{
    public class CustomerManager : AMS.DataAccess.Interfaces.ICustomerManager
    {
        public static readonly string CustomerTableName = "TC_Customer";
        public static readonly string ContactorTableName = "TC_Contactor";


        public List<CustomerInfo> GetMyPagedCustomers(CallContext context, int pageIndex, int pageSize)
        {
            int totalCount = 0;
            return GetPagedCustomersByCurrentUser(context, pageIndex, pageSize, false, out totalCount);
        }

        public List<CustomerInfo> GetMyPagedCustomers(CallContext context, int pageIndex, int pageSize, out int totalCount)
        {
            return GetPagedCustomersByCurrentUser(context, pageIndex, pageSize, true, out totalCount);
        }

        private List<CustomerInfo> GetPagedCustomersByCurrentUser(CallContext context, int pageIndex, int pageSize, bool caculateTotal, out int totalCount)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            sqlBuilder.Add("SELECT c.*");
            sqlBuilder.Add("    ,city.dName AS CityName");
            sqlBuilder.Add("    ,city.dProvinceName AS ProvinceName");
            sqlBuilder.Add("FROM TC_Customer c");
            sqlBuilder.Add("LEFT JOIN TD_City city ON c.CityId = city.diD");
            sqlBuilder.Add("WHERE");
            sqlBuilder.Add("    ", new SqlCondition("CreateUser", context.UserName));

            SqlInfo sql = new SqlInfo();
            sql.BaseSql = sqlBuilder.ToString();
            sql.PKField = "AID";
            sql.SoreExpression = "AID DESC";

            PagedSqlInfo pagedSqlInfo = new PagedSqlInfo();
            PagerWrapper.WrapSql(sql, new PagerInfo(pageIndex, pageSize), ref pagedSqlInfo);

            var ds = SqlDBHelper.Query(pagedSqlInfo.PagedSql);
            var list = BusinessObjectMapping.DataTableToBOList<CustomerInfo>(ds.Tables[0]);
            if (caculateTotal)
            {
                var totalDS = SqlDBHelper.Query(pagedSqlInfo.TotalCountSql);
                totalCount = AdoConverter.ToInt32(totalDS.Tables[0].Rows[0][0]);
            }
            else
            {
                totalCount = 0;
            }

            return list;
        }

        public CustomerInfo GetCustomer(int customerId)
        {
            string sql = "SELECT * FROM TC_Customer where AID=" + customerId.ToString();
            var ds = SqlDBHelper.Query(sql);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return BusinessObjectMapping.DataRowToBO<CustomerInfo>(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        #region ICustomerManager Members


        public List<CustomerInfo> GetPagedCustomers(CallContext context, CustomerFilter filter, int pageIndex, int pageSize)
        {
            int totalCount = 0;
            return GetPagedCustomers(context, filter, pageIndex, pageSize, false, out totalCount);
        }

        public List<CustomerInfo> GetPagedCustomers(CallContext context, CustomerFilter filter, int pageIndex, int pageSize, out int totalCount)
        {
            return GetPagedCustomers(context, filter, pageIndex, pageSize, true, out totalCount);
        }

        #endregion

        private List<CustomerInfo> GetPagedCustomers(CallContext context, CustomerFilter filter, int pageIndex, int pageSize, bool caculateTotal, out int totalCount)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            sqlBuilder.Add("SELECT c.*");
            sqlBuilder.Add("    ,city.dName AS CityName");
            sqlBuilder.Add("    ,city.dProvinceName AS ProvinceName");
            sqlBuilder.Add("FROM TC_Customer c");
            sqlBuilder.Add("LEFT JOIN TD_City city ON c.CityId = city.diD");
            sqlBuilder.Add("WHERE 1=1");

            if (filter != null)
            {
                if (filter.CustomerType != CustomerType.Unknown)
                {
                    sqlBuilder.Add("    AND ", new SqlCondition("CustomerType", (int)filter.CustomerType));
                }
                if (filter.BusinessType != CustomerBusinessType.Unspesified)
                {
                    sqlBuilder.Add("    AND ", new SqlCondition("BusinessType", (int)filter.BusinessType));
                }
                if (filter.ProvinceID != 0)
                {
                    sqlBuilder.Add("    AND ", new SqlCondition("ProvinceID", filter.ProvinceID));
                }
                if (filter.CityID != 0)
                {
                    sqlBuilder.Add("    AND ", new SqlCondition("CityID", filter.CityID));
                }
                if (!string.IsNullOrEmpty(filter.Name))
                {
                    sqlBuilder.Add("    AND ", new SqlCondition("Name", filter.Name, StringOperations.Like));
                }
            }

            SqlInfo sql = new SqlInfo();
            sql.BaseSql = sqlBuilder.ToString();
            sql.PKField = "AID";
            sql.SoreExpression = "AID DESC";

            PagedSqlInfo pagedSqlInfo = new PagedSqlInfo();
            PagerWrapper.WrapSql(sql, new PagerInfo(pageIndex, pageSize), ref pagedSqlInfo);

            var ds = SqlDBHelper.Query(pagedSqlInfo.PagedSql);
            var list = BusinessObjectMapping.DataTableToBOList<CustomerInfo>(ds.Tables[0]);
            if (caculateTotal)
            {
                var totalDS = SqlDBHelper.Query(pagedSqlInfo.TotalCountSql);
                totalCount = AdoConverter.ToInt32(totalDS.Tables[0].Rows[0][0]);
            }
            else
            {
                totalCount = 0;
            }

            return list;
        }

        public int AddCustomer(CallContext context, CustomerInfo customer)
        {
            SqlBatch batch = new SqlBatch();
            SqlBuilder sql = new SqlBuilder();

            SqlInsert sqlInsert = new SqlInsert(CustomerTableName);
            sqlInsert.AddBusinessObject(customer, typeof(CustomerInfo));
            sql.Add(sqlInsert.ToString());
            sql.Add("SELECT @@IDENTITY");
            batch.Add(sql);
#if SHOW_SQL
            Debug.WriteLine(batch.ToString());
#endif
            int newId = Convert.ToInt32(SqlDBHelper.GetSingle(batch.ToString()));
            customer.AID = newId;
            return newId;
        }

        public int UpdateCustomer(CallContext context, CustomerInfo customer)
        {
            SqlBatch batch = new SqlBatch();
            SqlUpdate sql = new SqlUpdate(CustomerTableName);
            sql.AddBusinessObject(customer, typeof(CustomerInfo));
            sql.AddCondition(new SqlCondition("AID", customer.AID));
            batch.Add(sql);
#if SHOW_SQL
            Debug.WriteLine(batch.ToString());
#endif
            int rowsCount = (int)SqlDBHelper.ExecuteSql(batch.ToString());
            return rowsCount;
        }

        public int DeleteCustomer(CallContext context, int[] customerIds)
        {
            SqlBatch batch = new SqlBatch();
            foreach (int customerId in customerIds)
            {
                SqlDelete sql = batch.CreateSqlDelete(CustomerTableName);
                sql.AddCondition(new SqlCondition("AID", customerId));
            }
#if SHOW_SQL
            Debug.WriteLine(batch.ToString());
#endif
            return SqlDBHelper.ExecuteSql(batch.ToString());
        }

        public List<CustomerInfo> GetCustomerSimpleList(CallContext cotext, QuerySimpleCustomerFilter filter)
        {
            SqlBuilder sql = new SqlBuilder();
            string top = string.Empty;
            if (filter.LimitedRowCount > 0)
            {
                top = "TOP " + filter.LimitedRowCount.ToString();
            }

            sql.Add("select ", top);
            sql.Add("AID, Name, Name_En, IDCode, Address, Tel");
            sql.Add("from TC_Customer");
            sql.Add("WHERE 1=1");
            if (filter != null)
            {
                if (filter.OnlyGetCustomer)
                {
                    sql.Add("    AND isCustomer = 1");
                }
                if (filter.OnlyGetApplicant)
                {
                    sql.Add("    AND isApplicant = 1");
                }
                if (filter.CustomerName != null || filter.CustomerNameE != null)
                {
                    SqlConditionGroup group = new SqlConditionGroup(LogicOperations.OR);
                    if (filter.CustomerName != null)
                    {
                        group.Add(new SqlCondition("Name", filter.CustomerName.Value, filter.CustomerName.Operation));
                    }
                    if(filter.CustomerNameE != null)
                    {
                        group.Add(new SqlCondition("Name_En", filter.CustomerName.Value, filter.CustomerName.Operation));
                    }

                    sql.Add("AND ", group.ToString());
                }
            }
#if SHOW_SQL
            Debug.WriteLine(sql.ToString());
#endif
            var ds = SqlDBHelper.Query(sql.ToString());
            var list = BusinessObjectMapping.DataTableToBOList<CustomerInfo>(ds.Tables[0]);
            return list;
        }
    }
}
